package dkbta.processing.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Collection;
import java.util.Iterator;
import java.util.Set;
import java.util.SortedMap;
import java.util.SortedSet;
import java.util.TreeMap;
import java.util.Map.Entry;

import dkbta.Config;
import dkbta.ontology.elements.Element;
import dkbta.ontology.elements.ElementDef;
import dkbta.ontology.elements.ElementType;
import dkbta.ontology.elements.event.Event;
import dkbta.ontology.elements.primitive.Primitive;
import dkbta.ontology.values.Values;
import dkbta.ontology.values.ValuesType;
import dkbta.processing.Patient;
import dkbta.processing.Patients;
import dkbta.util.Log;
import dkbta.util.time.TimeInterval;

/**
 * A data reader that gets its input from the database
 * 
 * @author Uri Kanonov
 */
public class DBDataReader{
	private static final boolean OUTPUT_ELEMENTS_TO_STDOUT = false;

	/** Whether invalid elements should be logged */
	private static final boolean LOG_INVALID_ELEMENTS = Config.getBoolean(
		"LOG_INVALID_ELEMENTS", true);

	/**
	 * Whether all of the elements for all patients should be read in one query (true) or
	 * each element per patient should be in a different query (false). Note that the
	 * latter doesn't require the transmitting of patient id and element name for each
	 * instance whereas the first does.
	 */
	private static final boolean READ_ALL_ELEMENTS_FROM_DB_AT_ONCE = Config.getBoolean(
		"READ_ALL_ELEMENTS_FROM_DB_AT_ONCE", true);

	private static final boolean ADVANCED_READ_ALL_ELEMENTS_FROM_DB_AT_ONCE_QUERY = Config
			.getBoolean("ADVANCED_READ_ALL_ELEMENTS_FROM_DB_AT_ONCE_QUERY", true);

	/** Databse info */
	private final DB _db;

	/** Reference to the patient container */
	private final Patients _patients;

	/** For each patient, its required primitives and events */
	private SortedMap<Patient, SortedSet<ElementDef>> _elementsPerPatients;

	/** Log handle */
	private final Log _log;

	private final String _allAtOnceQuery;

	/**
	 * Initializes a reader from the database
	 * 
	 * @param db The database
	 * @param patients A reference to the patient container
	 * @param allAtOnceQuery The query to be used in a "reading-all-at-once" mode
	 */
	public DBDataReader(DB db, Patients patients, String allAtOnceQuery){
		_db = db;
		_patients = patients;
		_allAtOnceQuery = allAtOnceQuery;
		_log = Log.getLogger();
	}

	/**
	 * Fully reads the data from the database
	 * 
	 * @throws DataException If an error has occured during the reading
	 */
	public void read() throws DataException{
		_log.fine("Started reading data from DB");
		try{
			if (READ_ALL_ELEMENTS_FROM_DB_AT_ONCE){
				readAllAtOnce();
			}else{
				readElementAtATime();
			}
		}catch(Exception e){
			throw new DataException("Unable to read data", e);
		}

		_log.fine("Finished reading data from DB");
	}

	/**
	 * Reads the data one element at a time
	 * 
	 * @throws DataException If an error has occured during the reading
	 */
	private void readElementAtATime() throws Exception{
		for (Patient currentPatient : _patients){
			// Obtaining the raw primitives and events that the patient requires
			Set<ElementDef> requiredPrimsAndEvents = currentPatient
					.getRequiredPrimsAndEvents();

			int pid = currentPatient.getPatientId();

			PreparedStatement ps = _db.getReadDataPS();
			if (ps == null){
				throw new Exception("Cannot obtain statement from DB connection");
			}

			for (ElementDef currentElement : requiredPrimsAndEvents){
				boolean isPrimitive = currentElement.getElementType() == ElementType.PRIMITIVE;
				String currentElementName = currentElement.getName();
				Values currentElementValues = currentElement.getValues();

				// SubjectID (?), FeatureName (?), StartTime, EndTime, FeatureValue
				ps.setInt(1, pid);
				ps.setString(2, currentElementName);
				ResultSet rs = ps.executeQuery();
				TimeInterval prevTI = null;
				while (rs.next()){
					long startTime = rs.getTimestamp(1).getTime();
					long endTime = rs.getTimestamp(2).getTime();
					double value = rs.getDouble(3);
					TimeInterval ti = new TimeInterval(startTime, endTime);

					prevTI = newElement(currentElementValues, isPrimitive,
						currentPatient, prevTI, pid, currentElementName, ti, value);
				}
			}
		}
	}

	/**
	 * Reads all the data in one query
	 * 
	 * @throws DataException If an error has occured during the reading
	 */
	private void readAllAtOnce() throws Exception{
		Connection conn = _db.getConnection();
		if (conn == null){
			throw new Exception("Unable to obtain connection DB");
		}
		Statement stmt = conn.createStatement();
		String query = constructQuery();
		if (ADVANCED_READ_ALL_ELEMENTS_FROM_DB_AT_ONCE_QUERY){
			query = _allAtOnceQuery;
		}
		ResultSet rs = stmt.executeQuery(query);

		Iterator<Entry<Patient, SortedSet<ElementDef>>> pIter = _elementsPerPatients
				.entrySet().iterator();
		Patient currentPatient = null;
		int currentPid = -1;
		Iterator<ElementDef> eIter = null;
		String currentElementName = null;
		Values currentElementValues = null;
		boolean isPrimitive = false;
		TimeInterval prevTI = null;
		while (rs.next()){
			int pid = rs.getInt(1);
			String elementName = rs.getString(2);
			long startTime = rs.getTimestamp(3).getTime();
			long endTime = rs.getTimestamp(4).getTime();
			double value = rs.getDouble(5);
			TimeInterval ti = new TimeInterval(startTime, endTime);

			
			// Finding the matching patient
			while (pid != currentPid){
				if (!pIter.hasNext()){
					throw new DataException(
							"Error in query. Either received an unknown patient, "
									+ "or data is not properly sorted in query");
				}

				Entry<Patient, SortedSet<ElementDef>> patientElements = pIter.next();
				currentPatient = patientElements.getKey();
				currentPid = currentPatient.getPatientId();

				eIter = patientElements.getValue().iterator();
			}
			
			// Finding the matching element
			while (!elementName.equals(currentElementName)){
				if (!eIter.hasNext()){
					throw new DataException(
							"Error in query. Either received an unknown element, "
									+ "or data is not properly sorted in query");
				}
				prevTI = null; // To note that it is a new element
				ElementDef currentElement = eIter.next();
				currentElementName = currentElement.getName();
				currentElementValues = currentElement.getValues();
				isPrimitive = currentElement.getElementType() == ElementType.PRIMITIVE;
			}

			prevTI = newElement(currentElementValues, isPrimitive, currentPatient,
				prevTI, pid, currentElementName, ti, value);
		}
		rs.close();
		stmt.close();
	}

	private TimeInterval newElement(Values currentElementValues, boolean isPrimitive,
		Patient currentPatient, TimeInterval prevTI, int pid, String elementName,
		TimeInterval ti, double value){
		Element e;
		if (isPrimitive){
			// Primitive
			Primitive prim = new Primitive(elementName, ti, value, ValuesType.NUMERIC);
			e = prim;
			if (currentElementValues.isValid(ValuesType.NUMERIC, value)){
				currentPatient.addPrimitive(prim);
			}else if (LOG_INVALID_ELEMENTS){
				_log.finer("Invalid element from DB: "
						+ prim.serializeTimeAndDate(pid, new StringBuilder()));
			}
		}else{
			// Event
			if (ti.equals(prevTI)){
				return prevTI;
			}
			Event ev = new Event(elementName, ti, null);
			currentPatient.addEvent(ev);
			e = ev;
		}

		if (OUTPUT_ELEMENTS_TO_STDOUT){
			System.out.println(e.serializeMillis(pid, null));
		}

		return ti;
	}

	private String constructQuery() throws Exception{
		String tableName = _db.getInputTableName();
		if (tableName == null){
			throw new Exception("DB Info didn't contain an input table name");
		}

		_elementsPerPatients = new TreeMap<Patient, SortedSet<ElementDef>>();
		StringBuilder query = new StringBuilder();

		query.append("SELECT SubjectID, FeatureName, StartTime, EndTime, FeatureValue\n");
		query.append("FROM ").append(tableName).append("\n");
		query.append("WHERE ");

		for (Iterator<Patient> pIter = _patients.iterator(); pIter.hasNext();){
			Patient patient = pIter.next();
			int pid = patient.getPatientId();

			// Obtaining the raw primitives and events that the patient requires
			SortedSet<ElementDef> requiredPrimsAndEvents = patient
					.getRequiredPrimsAndEvents();

			query.append("\n(SubjectID = '").append(pid).append("' AND FeatureName in (");
			appendElementNames(requiredPrimsAndEvents, query).append("))");
			if (pIter.hasNext()){
				query.append("\nOR ");
			}

			_elementsPerPatients.put(patient, requiredPrimsAndEvents);
		}

		query.append("\nORDER BY SubjectID ASC, FeatureName ASC, StartTime ASC");
		return query.toString();
	}

	private StringBuilder appendElementNames(Collection<ElementDef> eds, StringBuilder sb){
		Iterator<ElementDef> iter = eds.iterator();
		while (iter.hasNext()){
			ElementDef ed = iter.next();
			sb.append("'").append(ed.getName()).append("'");
			if (iter.hasNext()){
				sb.append(",");
			}
		}
		return sb;
	}
}